## 6.6. Managing Data

The first thing you do with a newly created table is add data to it. With the data in place, you may want to make changes and eventually remove it.

### 6.6.1. Inserts

Adding data to a table is one of the more straightforward concepts in SQL. You have already seen several examples of it in this book. Both MySQL and mSQL support the standard SQL `INSERT` syntax:

>     INSERT INTO table_name (column1, column2, ..., columnN)
>     VALUES (value1, value2, ..., valueN)

When inserting data into numeric fields, you can insert the value as is; for all other fields, you must wrap them in single quotes. For example, to insert a row of data into a table of addresses, you might issue the following command:

>     INSERT INTO addresses (name, address, city, state, phone, age)
>     VALUES('Irving Forbush', '123 Mockingbird Lane', 'Corbin', 'KY',
>            '(800) 555-1234', 26)

In addition, the escape character -- `\' by default -- enables you to escape single quotes and other literal instances of the escape character:

>     # Insert info for the directory Stacie's Directory which
>     # is in c:\Personal\Stacie
>     INSERT INTO files (description, location)
>     VALUES ('Stacie\'s Directory', 'C:\\Personal\\Stacie')

MySQL allows you to leave out the column names as long as you specify a value for every single column in the table in the exact same order they were specified in the table's `CREATE` call. If you want to use the default values for a column, however, you must specify the names of the columns for which you intend to insert nondefault data. If you do not have a default value set up for a column and that column is `NOT NULL`, you must include that column in the `INSERT` statement with a non-`NULL` value. If the earlier files table had contained a column called `size`, then the default value would be used. Under mSQL, the default value is always `NULL`. MySQL allows you to specify a custom default value in the table's `CREATE`.

Newer versions of MySQL support a nonstandard `INSERT` call for inserting multiple rows at once:

>     INSERT INTO foods VALUES (NULL, 'Oranges', 133, 0, 2, 39),
>                              (NULL, 'Bananas', 122, 0, 4, 29),
>                              (NULL, 'Liver', 232, 3, 15, 10)

> #### NOTE

> While these nonstandard syntaxes supported by MySQL are useful for quick system administration tasks, you should not use them when writing database applications unless you really need the speed benefit they offer. As a general rule, you should stick as close to the ANSI SQL2 standard as MySQL and mSQL will let you. By doing so, you are making certain that you can move to some other database at some point in the future. Being flexible is especially critical for people with mid-range database needs because such users generally hope one day to become people with high-end database needs.

MySQL supports the SQL2 syntax for inserting the values of a `SELECT` call into a table:

>     INSERT INTO foods (name, fat)
>     SELECT food_name, fat_grams FROM recipes

You should note that the number of columns in the `INSERT` matches the number of columns in the `SELECT`. In addition, the datatypes for the `INSERT` columns must match the datatypes for the corresponding `SELECT` columns. Finally, the `SELECT` clause in an `INSERT` statement cannot contain an `ORDER BY` modifier and cannot be selected from the same table where the `INSERT` is occurring.

### 6.6.2. Updates

The insertion of new rows into a database is just the start of database use. Unless your database is read-only, you will probably also need to make periodic changes to the data. The standard SQL modification statement looks like this:

>     UPDATE table_name
>     SET column1=value1, column2=value2, ..., columnN=valueN
>     [WHERE clause]

Under mSQL, the value you assign to a column must be a literal of the column's datatype. MySQL, in contrast, enables you to calculate the assigned value. You can even calculate the value based on a value in another column:

>     UPDATE years
>     SET end_year = begin_year+5

This command sets the value in the `end_year` column equal to the value in the `begin_year` column plus 5 for each row in that table.

### 6.6.3. The WHERE Clause

You probably noted something earlier called the `WHERE` clause. In SQL, a `WHERE` clause enables you to pick out specific rows in a table by specifying a value that must be matched by the column in question. For example:

>     UPDATE bands
>     SET lead_singer = 'Ian Anderson'
>     WHERE band_name = 'Jethro Tull'

This `UPDATE` specifies that you should only change the `lead_singer` column for the row where `band_name` is identical to "Jethro Tull." If the column in question is not a unique index, that `WHERE` clause may match multiple rows. Many SQL commands employ `WHERE` clauses to help pick out the rows on which you wish to operate. Because the columns in the `WHERE` clause are columns on which you are searching, you should generally have indices created around whatever combinations you commonly use.

### 6.6.4. Deletes

Deleting data is a very straightforward operation. You simply specify the table from which you want to delete followed by a `WHERE` clause that identifies the rows you want to delete:

>     DELETE FROM table_name [WHERE clause]

As with other commands that accept a `WHERE` clause, the `WHERE` clause is optional. In the event you leave out the `WHERE` clause, you will delete all of the records in the table! Of all destructive commands in SQL, this is the easiest one to issue mistakenly.

Copyright © 2001 O'Reilly & Associates. All rights reserved.